package com.sff.easyexcel.test.demo.template;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.sff.easyexcel.test.demo.annotation.MergeCell;
import com.sff.easyexcel.test.demo.util.BeanUtils;
import com.sff.easyexcel.test.demo.util.FilePathUtil;
import lombok.Data;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 *  手机订单、收货区域合并导出
 *  1.一对多合并导出
 *  https://juejin.cn/post/7051751438715715620
 */
public class MergerCellDataByXiaoMi {

    /** 自定义单元格合并策略 */
    private static class CustomMergeStrategy implements RowWriteHandler {
        /** 需要合并的列的下标 */
        private Integer pkIndex;
        /** 需要合并的列的下标集合 */
        private List<Integer> needMergeColumnIndex = new ArrayList<>();
        /** 被导出的数据类型 */
        private Class<?> elementType;

        public CustomMergeStrategy(Class<?> elementType) {
            this.elementType = elementType;
        }

        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
            // 如果是标题,则直接返回
            if (isHead) {
                return;
            }

            Sheet sheet = writeSheetHolder.getSheet();
            if (null == pkIndex) {
                this.lazyInit(writeSheetHolder);
            }

            /** (1)判断是否需要和上一行进行合并 (2)不能和标题合并，只能数据行之间合并 */
            if (row.getRowNum() <= 1) {
                return;
            }

            /** 将当前行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并 */
            Row lastRow = sheet.getRow(row.getRowNum() - 1);
            if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
                for (Integer needMerIndex : needMergeColumnIndex) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(), needMerIndex, needMerIndex);
                    sheet.addMergedRegionUnsafe(cellRangeAddress);
                }
            }
        }

        /** 初始化主键下标和需要合并字段的下标 */
        private void lazyInit(WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            Row titleRow = sheet.getRow(0);

            Class<?> eleType = this.elementType;
            Field[] fields = eleType.getDeclaredFields();

            // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
            for (Field theField : fields) {
                // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
                ExcelProperty excelProperty = theField.getAnnotation(ExcelProperty.class);
                if (null == excelProperty) {
                    continue;
                }

                // 获取自定义的注解，有该注解则表示单元格需要合并
                MergeCell mergeCell = theField.getAnnotation(MergeCell.class);
                if (null == mergeCell) {
                    continue;
                }

                for (int index = 0; index < fields.length; index++) {
                    Cell theCell = titleRow.getCell(index);
                    // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
                    if (null == theCell) {
                        continue;
                    }
                    // 将字段和excel的表头匹配上
                    if (excelProperty.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
                        if (mergeCell.isPk()) {
                            pkIndex = index;
                        }

                        if (mergeCell.needMerge()) {
                            needMergeColumnIndex.add(index);
                        }
                    }
                }
            }

            // 没有指定主键，则异常
            if (null == this.pkIndex) {
                throw new IllegalStateException("使用@MergeCell注解必须指定主键");
            }
        }

    }

    public static void main(String[] args) {
        List<OrderPO> data = getOrderPOData();
        List<OrderDTO> orderDataList = getExportOrderData(data);

        String writeFileName = FilePathUtil.getPath() + "mi-order-" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(writeFileName)
                .head(OrderDTO.class)
                .registerWriteHandler(new CustomMergeStrategy(OrderDTO.class))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("订单列表")
                .doWrite(orderDataList);
    }

    /** 将数据转化成导出格式 */
    private static List<OrderDTO> getExportOrderData(List<OrderPO> orderList) {
        List<OrderDTO> result = new ArrayList<>();
        for (OrderPO order : orderList) {

            List<ProductPO> productList = order.getProductList();
            for (ProductPO product : productList) {
                OrderDTO orderData = new OrderDTO();
                BeanUtils.copyProperties(product, orderData);
                BeanUtils.copyProperties(order, orderData);
                result.add(orderData);
            }
        }
        return result;
    }

    /** 1.嵌套订单信息打散,方便进行导出
     *  2.二级表头通过设置 @ExcelProperty 的value为数组来实现 */
    @Data
    private static class OrderDTO {

        @ColumnWidth(20)
        @ExcelProperty(value = "订单编码")
        @MergeCell(needMerge = true, isPk = true)
        private String orderId;

        @ColumnWidth(20)
        @ExcelProperty(value = "收货地址")
        @MergeCell(needMerge = true, isPk = true)
        private String receiverAddress;

        @ColumnWidth(20)
        @ExcelProperty(value = {"商品信息", "商品编码"})
        private String productSn;

        @ColumnWidth(20)
        @ExcelProperty(value = {"商品信息", "商品名称"})
        private String name;

        @ColumnWidth(30)
        @ExcelProperty(value = {"商品信息", "商品标题"})
        private String subTitle;

        @ColumnWidth(20)
        @ExcelProperty(value = {"商品信息", "品牌名称"})
        private String brandName;

        @ColumnWidth(20)
        @ExcelProperty(value = {"商品信息", "商品价格"})
        private BigDecimal price;

        @ColumnWidth(20)
        @ExcelProperty(value = {"商品信息", "商品数量"})
        private Integer count;
    }

    private static List<OrderPO> getOrderPOData() {
        List<OrderPO> orderList = Lists.newArrayList();

        OrderPO order = new OrderPO();
        String orderId = "M" + System.currentTimeMillis();
        order.setOrderId(orderId);
        order.setReceiverAddress("北京市朝阳区");

        List<ProductPO> productList = getProductList();
        order.setProductList(productList);
        orderList.add(order);

        OrderPO order1 = new OrderPO();
        order1.setOrderId(orderId);
        order1.setReceiverAddress("北京市顺义区");
        order1.setProductList(productList);
        orderList.add(order1);
        return orderList;
    }

    private static List<ProductPO> getProductList() {
        List<ProductPO> productList = Lists.newArrayList();
        ProductPO product = new ProductPO();
        product.setProductSn("78898");
        product.setName("小米13");
        product.setSubTitle("全网通,6GB+128GB");
        product.setBrandName("小米");
        product.setCount(30);
        product.setPrice(BigDecimal.valueOf(3599));
        productList.add(product);

        ProductPO product1 = new ProductPO();
        product1.setProductSn("68898");
        product1.setName("iPhone 13");
        product1.setSubTitle("全网通,6GB+128GB");
        product1.setBrandName("苹果");
        product1.setCount(40);
        product1.setPrice(BigDecimal.valueOf(6599));
        productList.add(product1);
        return productList;
    }

    @Data
    private static class OrderPO {
        /**订单ID*/
        private String orderId;
        /**收货地址*/
        private String receiverAddress;
        /**商品信息*/
        private List<ProductPO> productList;
    }

    @Data
    private static class ProductPO {
        /** 商品编码 */
        private String productSn;
        /** 商品名称 */
        private String name;
        /**商品标题*/
        private String subTitle;
        /**商品品牌*/
        private String brandName;
        /**商品价格*/
        private BigDecimal price;
        /**商品数量*/
        private Integer count;
    }
}
